from django.test import TestCase
import numpy as np
import operator
import os
import sys
from django.db.models import Count,Min,Max,Sum
from django.db.models import Q,Avg
if __name__ == "__main__":
    os.environ.setdefault("DJANGO_SETTINGS_MODULE", "JobWeb.settings")
    import django
    django.setup()
    from job import models
    # num = models.Job.objects.values('location').annotate(num=Count('location'))
    # #所有城市职位数
    # city = {}
    # for i in num.all():
    #     j = i['location'].split('-')[0]
    #     if j not in city:
    #         # data = {'location':j,'num':i['num']}
    #         city[j] = i['num']
    #     else:
    #         city[j] += i['num']
    #     # print(i)
    # print(city)
    # result_map = []
    # for i in city:
    #     data = {'city':i,'value':city[i]}
    #     result_map.append(data)
    # result_map = sorted(result_map,key=operator.itemgetter('value'))[-20:]
    # print(result_map)
    # base_bar_data = []
    # for i in city:
    #     data = [i,city[i]]
    #     base_bar_data.append(data)
    # base_bar_data = sorted(base_bar_data, key=operator.itemgetter(1), reverse=True)[:20]
    # print(base_bar_data)
    # for i in num.all().order_by('num'):
    #     if i['location'].split('-')[0] == "北京":
    #         print(i)
    #         pass
    # ########################获取所有城市#####
    # c = []
    # for i in num.all().order_by('num'):
    #     j = i['location'].split('-')[0]
    #     if j not in c:
    #         c.append(j)
    # # print(c)
    # bar_data = []
    # for i in base_bar_data:
    #     data = {'name': i[0], 'y': i[1], 'drilldown': i[0]}
    #     bar_data.append(data)
    # print(bar_data)
    #可下钻饼图 数据格式[{'type':'pie','id',city,'data':[['area',value],..]},...]
    # pie_data = []
    # for i in base_bar_data:
    #     area = []
    #     for j in num.all():
    #         if j['location'].split('-')[0] == i[0]:
    #             area_data = [j['location'], j['num']]
    #             area.append(area_data)
    #     city_data = {'type': 'pie', 'id': i[0], 'data': area}
    #     pie_data.append(city_data)
    # print(pie_data)
    # num = models.Job.objects.values('positionname').annotate(num=Count('positionname'))
    # print(num)
    # keyword = []
    # for i in num:
    #     data = [i['positionname'], i['num']]
    #     keyword.append(data)
    # keyword = sorted(keyword, key=operator.itemgetter(1), reverse=True)[:30]
    # print(keyword)
    # position_num = []
    # for i in keyword:
    #     data = {'name': i[0], 'value': i[1]}
    #     position_num.append(data)
    # print(position_num)


    # min = models.Job.objects.values('key_word').annotate(min_avg = Avg('salary_min'))
    # max = models.Job.objects.values('key_word').annotate(max_avg = Avg('salary_max'))
    # min_avg = [round(x['min_avg'],2) for x in min]
    # max_avg = [round(y['max_avg'],2) for y in max]
    # z = list(zip(min_avg,max_avg))
    # avg_list = list(np.mean(z,1))
    # print(sorted(avg_list,reverse=True))
    # salary_avg = []
    # for i in range(len(avg_list)):
    #     data = {'name': min[i]['key_word'], 'value': avg_list[i]}
    #     salary_avg.append(data)
    # print(salary_avg)
    # num = models.JobAll.objects.values('work_exp').annotate(num=Count('work_exp'))
    # print(num)
    # key_salary = []
    # data = models.Job.objects.values('key_word').annotate(num=Count('key_word'))
    # for i in data:
    #     read_data = models.Job.objects.filter(Q(key_word__contains=i['key_word'])).values('salary','key_word').annotate(num=Count('key_word'))
    #     read_data = sorted(read_data, key=operator.itemgetter('num'), reverse=True)
    #     print(read_data)
    #     L = []
    #     for j in read_data:
    #         L.append([j['salary'], j['num']])
    #     data = {'name': i['key_word'], 'id': i['key_word'], 'data': L[:10]}
    #     key_salary.append(data)
    #
    # print(key_salary)
    # [{'name':edu,data:[,,,,]}]

    # list1 = ['博士','硕士','本科','大专']
    # list2 = ['软件工程师','Java开发工程师','Hadoop工程师','系统架构设计师','大数据开发工程师','Go开发工程师','C/C++开发工程师','ERP技术开发','.NET开发工程师','PHP开发工程师','多媒体开发工程师','爬虫开发工程师','Ruby开发工程师','Python开发工程师','系统分析员','技术文档工程师','脚本开发工程师','区块链开发']
    # result = []
    # for i in list1:
    #     L = []
    #     data = {'name': i,'data':L}
    #     for j in list2:
    #         read_data = models.JobC.objects.filter(edu_background__contains=i,key_word__contains=j).count()
    #         L.append(read_data)
    #     result.append(data)
    # print(result)
    # read_data = models.JobC.objects.values('edu_background').annotate(Count('edu_background'))
    # for i in read_data:
    #     print(i)


    def chart_data():
        read_data = models.JobC.objects.values('location').annotate(num=Count('location'))
        # 所有城市职位数city
        # 得到各城市职业数，数据格式：{city:value}
        city = {}
        for i in read_data.all():
            j = i['location'].split('-')[0]
            if j not in city:
                city[j] = i['num']
            else:
                city[j] += i['num']
        # base_bar_data数据格式：[[city,value],[],..],用于简单柱状图/饼状图
        base_bar_data = []
        for i in city:
            data = [i, city[i]]
            base_bar_data.append(data)
        base_bar_data = sorted(base_bar_data, key=operator.itemgetter(1), reverse=True)[:20]

        # 可下钻柱状图/饼状一层/可带图例 数据格式：[{'name':city,'y':value,'drilldown':city},...]
        bar_data = []
        for i in base_bar_data:
            data = {'name': i[0], 'y': i[1], 'drilldown': i[0]}
            bar_data.append(data)

        # 数据格式：[{'city':city,'value':value},...]
        result_map = []
        for i in city:
            data = {'city': i, 'value': city[i]}
            result_map.append(data)

        # 可下钻饼图二层/可带图例 数据格式[{'type':'pie','id',city,'data':[['area',value],...]},...]
        pie_data = []
        for i in base_bar_data:
            area = []
            for j in read_data.all():
                if j['location'].split('-')[0] == i[0]:
                    area_data = [j['location'],j['num']]
                    area.append(area_data)
            city_data = {'type':'pie','id':i[0],'data':area}
            pie_data.append(city_data)

        # 词云图 数据格式[[positionname,num],...]

        read_data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
        wordcloud = []
        for i in read_data:
            data = [i['key_word'], i['num']]
            wordcloud.append(data)
        wordcloud = sorted(wordcloud, key=operator.itemgetter(1), reverse=True)
        # 可下行数据格式[{'name': city, 'y': value, 'drilldown': city}, ...]

        position_num = []
        for i in wordcloud:
            data = {'name': i[0], 'y': i[1], 'drilldown': i[0]}
            position_num.append(data)

        read_data = sorted(models.JobC.objects.values('positionname').annotate(num=Count('positionname')),
                           key=operator.itemgetter('num'), reverse=True)[:20]
        position_detail = []
        for i in read_data:
            data = {'name': i['positionname'], 'y': i['num']}
            position_detail.append(data)

        # 各城市在职业中的占比
        key_city = []
        data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
        for i in data:
            read_data = models.JobC.objects.filter(Q(key_word__contains=i['key_word'])).values('area',
                                                                                               'key_word').annotate(
                num=Count('key_word'))
            L = []
            for j in read_data:
                L.append([j['area'], j['num']])
            data = {'type': 'pie', 'id': i['key_word'], 'data': L[:10]}
            key_city.append(data)

        # 简单柱状带图例 数据格式 {'name':i[0],'data':[{'name':i[0],'y':i[1]}]}
        min = models.JobC.objects.values('key_word').annotate(min_avg=Avg('salary_min'))
        max = models.JobC.objects.values('key_word').annotate(max_avg=Avg('salary_max'))
        min_avg = [round(x['min_avg'], 2) for x in min]
        max_avg = [round(y['max_avg'], 2) for y in max]
        z = list(zip(min_avg, max_avg))
        avg_list = list(np.mean(z, 1))
        salary_temp = []
        for i in range(len(avg_list)):
            data = [min[i]['key_word'], avg_list[i]]
            salary_temp.append(data)
        salary_temp = sorted(salary_temp, key=operator.itemgetter(1), reverse=True)
        salary_avg = []
        for i in salary_temp:
            data = {'name': i[0],
                    'data': [{'name': i[0], 'y': i[1], 'drilldown': i[0]}]}
            salary_avg.append(data)

        key_salary = []
        data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
        for i in data:
            read_data = models.JobC.objects.filter(Q(key_word__contains=i['key_word'])).values('salary',
                                                                                               'key_word').annotate(
                num=Count('key_word'))
            read_data = sorted(read_data, key=operator.itemgetter('num'), reverse=True)
            L = []
            for j in read_data:
                L.append([j['salary'], j['num']])
            data = {'name': i['key_word'], 'id': i['key_word'], 'data': L[:10]}
            key_salary.append(data)
        key_exp = []
        data = models.JobC.objects.values('key_word').annotate(num=Count('key_word'))
        for i in data:
            read_data = models.JobC.objects.filter(Q(key_word__contains=i['key_word'])).values('work_exp').annotate(
                num=Count('work_exp'))
            L = []
            for j in read_data:
                L.append([j['work_exp'], j['num']])
            data = {'type': 'pie', 'id': i['key_word'], 'data': L[:10]}
            key_exp.append(data)

        result = [bar_data, pie_data, wordcloud, position_num, key_city, salary_avg, key_salary, position_detail,key_exp]
        return result
    print(chart_data())
